package com.yingdong.tools;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.eclipse.core.resources.IFile;
import org.eclipse.core.resources.IProject;
import org.eclipse.core.resources.IResource;

import freemarker.template.Configuration;
import freemarker.template.Template;

public class DBSchemaGenerator {

	public static String transDBTypeToJavaType(String dbType) {
		Map<String, String> map = new HashMap<>();
		map.put("VARCHAR", "String");
		map.put("CHAR", "String");
		map.put("TEXT", "String");
		map.put("CHAR", "String");
		map.put("INT", "Integer");
		map.put("TINYINT", "Integer");
		map.put("SMALLINT", "Integer");
		map.put("MEDIUMINT", "Integer");
		map.put("INTEGER", "Integer");
		map.put("REAL", "Float");
		map.put("ID", "Long");
		map.put("FLOAT", "Double");
		map.put("DOUBLE", "Double");
		map.put("BIGINT", "Long");
		map.put("BIT", "Integer");
		map.put("DECIMAL", "Double");
		map.put("INT UNSIGNED", "Integer");
		map.put("DATE", "Date");
		map.put("TIME", "Time");
		map.put("TIMESTAMP", "Timestamp");
		return map.get(dbType);
	}

	public static void main(String[] args) throws Exception {

		Configuration cfg = new Configuration();
		cfg.setDefaultEncoding("UTF-8");
		// 设置模板文件所在的目录
		cfg.setClassForTemplateLoading(ExcelTemplateGenerator.class, "/template");
		Writer out = null;

		// 取得模板文件
		Template t = cfg.getTemplate("DBSchema.ftl");

		Class.forName("com.mysql.jdbc.Driver").newInstance();
		Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.18.48:3306/card_db?user=root&password=root");
		DatabaseMetaData m_DBMetaData = conn.getMetaData();
		ResultSet tableRet = m_DBMetaData.getTables(null, "%", "%", new String[] { "TABLE" });
		while (tableRet.next()) {
			String tableName = tableRet.getString("TABLE_NAME");
			if (!tableName.contains("u_po_"))
				continue;
			tableName = tableName.substring(0, 1).toUpperCase() + tableName.substring(1);
			ResultSet colRet = m_DBMetaData.getColumns(null, "%", tableName, "%");
			Map<String, DBSchemaField> typeMap = new HashMap<>();
			List<DBSchemaField> fields = new ArrayList<>();
			List<DBSchemaField> keys = new ArrayList<>();
			String autoIncreamentType = "";
			while (colRet.next()) {
				String columnName = colRet.getString("COLUMN_NAME");
				String columnType = transDBTypeToJavaType(colRet.getString("TYPE_NAME"));
				String comment = colRet.getString("REMARKS");
				int datasize = colRet.getInt("COLUMN_SIZE");
				int digits = colRet.getInt("DECIMAL_DIGITS");
				int nullable = colRet.getInt("NULLABLE");
				Object object = colRet.getObject("COLUMN_DEF");
				String autoIncreament = colRet.getString("IS_AUTOINCREMENT");
				if (autoIncreament.equals("YES")) {
					autoIncreamentType = columnType;
				}
				DBSchemaField schema = new DBSchemaField(columnType, comment, columnName, false, autoIncreament.equals("YES"), object, colRet.getString("TYPE_NAME").toUpperCase());
				typeMap.put(columnName, schema);
				fields.add(schema);

			}
			ResultSet ids = m_DBMetaData.getPrimaryKeys(null, "%", tableName);
			while (ids != null && ids.next()) {
				String col = ids.getString("COLUMN_NAME");
				typeMap.get(col).setPrim(true);
				keys.add(typeMap.get(col));
			}
			String fileName = tableName;
			Map root = new HashMap();

			root.put("fields", fields);
			root.put("classname", fileName);
			root.put("keys", keys);
			root.put("autoIncreamentType", autoIncreamentType);
			out = new FileWriter("src/main/java/com/yingdong/db/" + fileName + ".java");
			t.process(root, out);
		}

	}

	public static void generateDBSchemaTemplate(IFile file) throws Exception {
		if (!file.getFileExtension().equals("xml")) {
			throw new Exception("文件不是标准的xml类型");
		}
		Configuration cfg = new Configuration();
		cfg.setDefaultEncoding("UTF-8");
		// 设置模板文件所在的目录
		cfg.setClassForTemplateLoading(ExcelTemplateGenerator.class, "/template");
		Template t = cfg.getTemplate("DaoTemplate.ftl");

		File f = file.getLocation().makeAbsolute().toFile();
		Document doc = null;
		SAXReader reader = new SAXReader();
		doc = reader.read(f);
		Element root = doc.getRootElement();
		if (!root.getName().equals("generatorConfiguration")) {
			throw new Exception("xml文件格式错误,无法生成");
		}
		Element element = root.element("context").element("jdbcConnection");
		Class.forName(element.attributeValue("driverClass")).newInstance();
		Properties properties = new Properties();
		properties.put("user", element.attributeValue("user"));
		properties.put("password", element.attributeValue("password"));
		Connection conn = DriverManager.getConnection(element.attributeValue("connectionURL"), properties);
		DatabaseMetaData m_DBMetaData = conn.getMetaData();
		element = root.element("context").element("table");
		String tableName = element.attributeValue("tableName");
		String domainName = element.attributeValue("domainObjectName");

		ResultSet colRet = m_DBMetaData.getColumns(null, "%", tableName, "%");
		Map<String, DBSchemaField> typeMap = new HashMap<>();
		List<DBSchemaField> fields = new ArrayList<>();
		List<DBSchemaField> keys = new ArrayList<>();
		String autoIncreamentType = "";
		while (colRet.next()) {
			String columnName = colRet.getString("COLUMN_NAME");
			String columnType = transDBTypeToJavaType(colRet.getString("TYPE_NAME"));
			String comment = colRet.getString("REMARKS");
			int datasize = colRet.getInt("COLUMN_SIZE");
			int digits = colRet.getInt("DECIMAL_DIGITS");
			int nullable = colRet.getInt("NULLABLE");
			Object object = colRet.getObject("COLUMN_DEF");
			if (object == null) {
				object = "null";
			} else {
				String type = columnType;
				switch (type) {
				case "String":
					object = "\"" + object + "\"";
					break;
				case "Date":
					object = "Date.valueOf(" + "\"" + object + "\"" + ")";
					break;
				case "Time":
					object = "Time.valueOf(" + "\"" + object + "\"" + ")";
					break;
				case "Timestamp":
					object = "Timestamp.valueOf(" + "\"" + object + "\"" + ")";
					break;
				default:
					break;
				}
			}
			String autoIncreament = colRet.getString("IS_AUTOINCREMENT");
			if (autoIncreament.equals("YES")) {
				autoIncreamentType = columnType;
			}
			DBSchemaField schema = new DBSchemaField(columnType, comment, columnName, false, autoIncreament.equals("YES"), object, colRet.getString("TYPE_NAME").toUpperCase().replace("INT", "INTEGER"));
			typeMap.put(columnName, schema);
			fields.add(schema);

		}
		ResultSet ids = m_DBMetaData.getPrimaryKeys(null, "%", tableName);
		while (ids != null && ids.next()) {
			String col = ids.getString("COLUMN_NAME");
			typeMap.get(col).setPrim(true);
			keys.add(typeMap.get(col));
		}

		Map param = new HashMap();

		param.put("fields", fields);
		param.put("classname", domainName);
		param.put("tablename", tableName);
		param.put("keys", keys);
		param.put("autoIncreamentType", autoIncreamentType);
		Writer out = new StringWriter();
		t.process(param, out);
		IProject project = file.getProject();
		IFile writeFile = project.getFile("src/com/yingdong/db/dao/" + domainName + "Dao.java");

		InputStream source = new ByteArrayInputStream(out.toString().getBytes("utf8"));
		if (writeFile.exists()) {
			writeFile.delete(true, null);
		}
		writeFile.create(source, IResource.FILE, null);
		source.close();
		out.close();
		t = cfg.getTemplate("DomainTemplate.ftl");
		out = new StringWriter();
		t.process(param, out);
		project = file.getProject();
		writeFile = project.getFile("src/com/yingdong/db/domain/" + domainName + ".java");

		source = new ByteArrayInputStream(out.toString().getBytes("utf8"));
		if (writeFile.exists()) {
			writeFile.delete(true, null);
		}
		writeFile.create(source, IResource.FILE, null);
		source.close();
		out.close();
	}

}
